﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Utility;

namespace YouHP.SqlServerDAL
{
    public class PatientDAL : IPatient
    {
        #region 病人疾病信息查询以及添加
        /* 根据用户取得其疾病信息*/
        public PatientRecordInfo GetPatientRecordByUserId(int userId)
        {
            string condstr = string.Format(" [patientid]={0} ", userId);
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSP_GetPatientRecord,
                condstr);
            return CBO.FillObject<PatientRecordInfo>(reader);
        }

        public PatientRecordInfo GetPatientRecordById(int id)
        {
            string condstr = string.Format(" [patientrecord].[id]={0} ", id);
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSP_GetPatientRecord,
                condstr);
            return CBO.FillObject<PatientRecordInfo>(reader);
        }

        /*添加疾病纪录信息*/
        public bool AddDiseaseRecorde(PatientRecordInfo info)
        {
            int updateTime = 0;
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_AddPatientRecord,
                info.PatientId, info.DiseaseId,
                info.RecordDate, info.UpdateDate,info.LattestEmailHintDate,
                info.Remark,
                info.StartDate,
                info.ConfirmDate,
                info.ConfirmHospital,
                updateTime
                );
            return cnt > 0;
        }

        /*修改疾病纪录的某些信息*/
        public bool UpdateDiseaseRecorde(PatientRecordInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_UpdatePatientRecord,
                info.Id,
                info.DiseaseId,
                info.UpdateDate,
                info.LattestEmailHintDate,
                info.Remark,
                info.StartDate,
                info.ConfirmDate,
                info.ConfirmHospital
                );
            return cnt > 0;
        }

        public bool UpdatePatientRecord_GraphGeneralPath(PatientRecordInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
               Common.PatientSP_UpdatePatientRecord_GraphGeneralPath,
               info.Id,
               info.GraphGeneralPath);
            return cnt > 0;
        }

        public bool UpdatePatientRecord_GraphLabcheckPath(PatientRecordInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
              Common.PatientSP_UpdatePatientRecord_GraphLabcheckPath,
              info.Id,
              info.GraphIndicatorPath);
            return cnt > 0;
        }

        public bool UpdatePatientRecord_GraphSymptomPath(PatientRecordInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
              Common.PatientSP_UpdatePatientRecord_GraphSymptomPath,
              info.Id,
              info.GraphSymptomPath);
            return cnt > 0;
        }

        public bool UpdatePatientRecord_GraphTreatmentPath(PatientRecordInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
              Common.PatientSP_UpdatePatientRecord_GraphTreamtmentPath,
              info.Id,
              info.GraphTreatmentPath);
            return cnt > 0;
        }

        #endregion

        #region 病人症状记录 （废弃的）

        public bool AddSymptomRecordes(SymptomRecordesInfo[] ptInfos)
        {
            return false;
        }

        /*批量修改某天症状信息*/
        public bool UpdateSymptomRecordes(SymptomRecordesInfo[] ptInfos)
        {
            return false;
        }

        /*批量添加某天症状信息*/
        public bool AddSymptomRecordes(SymptomRecordesInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_AddSymptomRecordes,
                info.PatientRecordId,
                info.RecordDate, info.UpdateDate,
                info.SymptomInfo,info.Remark);
            return cnt > 0;
        }

        public bool UpdateSymptomRecordes(SymptomRecordesInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_UpdateSymptomRecordes,
                info.Id, info.PatientRecordId,
                info.RecordDate, info.UpdateDate,
                info.SymptomInfo,info.Remark);
            return cnt > 0;
        }

        public bool DelSymptomRecordes(int prid,int[] itemIds)
        {
            int cnt = itemIds.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" [prid]={0} and ", prid);
            sb.Append("[id] in (");
            sb.Append(itemIds[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(itemIds[i]);
            }
            sb.Append(")");

            int cnt2 = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_DelSymptomRecordes,
                sb.ToString()) ;
            return cnt2 > cnt;
        }


        /*根据病人疾病id以及当天日期获取已经填写的症状信息*/
         public List<SymptomRecordesInfo> GetSymptomRecordesList(int pageSize,int pageIndex,int prId, DateTime date)
        {
            string condstr = " prid=" + prId;
            int count;
            return GetSymptomRecordesList(pageSize, pageIndex, condstr, out count);
        }

         private List<SymptomRecordesInfo> GetSymptomRecordesList(int pageSize, int pageIndex, string condstr, out int count)
         {
             DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
               Common.PatientSP_GetSymptomRecordesList,
               pageSize, pageIndex, condstr);

             IDataReader dr = dset.Tables[0].CreateDataReader();
             if (dr != null && dr.Read())
             {
                 count = dr.GetInt32(0);
                 dr.Close();
             }
             else
             {
                 count = 0;
             }

             dr = dset.Tables[1].CreateDataReader();
             return CBO.FillCollection<SymptomRecordesInfo>(dr);
         }

        public SymptomRecordesInfo GetSymptomRecordes(int id,int prid)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSP_GetSymptomRecordes,
                id);
            return CBO.FillObject<SymptomRecordesInfo>(reader);
        }

        #endregion

        #region 病人实验室指标  （废弃的）

        // 添加一条症状记录 
        public bool AddLabcheckRecordes(LabcheckRecordesInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_AddLabcheckRecordes,
                info.PRId,
                info.RecordDate, info.UpdateDate,
                info.LabcheckInfo,info.Remark);
            return cnt > 0;
        }

        public bool UpdateLabcheckRecordes(LabcheckRecordesInfo info)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_UpdateLabcheckRecordes,
                info.Id, info.PRId,
                info.RecordDate, info.UpdateDate,
                info.LabcheckInfo, info.Remark);
            return cnt > 0;
        }

        public bool DelLabcheckRecordes( int[] idLst,int prid)
        {
            int cnt = idLst.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(" [prid]={0} and ", prid);
            sb.Append("[id] in (");
            sb.Append(idLst[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(idLst[i]);
            }
            sb.Append(")");

            int cnt2 = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_DelLabcheckRecordes,
                sb.ToString());
            return cnt2 > cnt;
        }


        /*根据病人疾病id以及当天日期获取已经填写的症状信息*/
        public List<LabcheckRecordesInfo> GetLabcheckRecordesList(int pageSize, int pageIndex, int prId, out int count)
        {
            string condstr = " prid=" + prId;
            return GetLabcheckRecordesList(pageSize, pageIndex, condstr, out count);
        }

        private List<LabcheckRecordesInfo> GetLabcheckRecordesList(int pageSize, int pageIndex, string condstr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
              Common.PatientSP_GetLabcheckRecordesList,
              pageSize, pageIndex, condstr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = dset.Tables[1].CreateDataReader();
            return CBO.FillCollection<LabcheckRecordesInfo>(dr);
        }

        public LabcheckRecordesInfo GetLabcheckRecordes(int id, int prid)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSP_GetLabcheckRecordes,
                id);
            return CBO.FillObject<LabcheckRecordesInfo>(reader);
        }

        #endregion

        #region 病人治疗信息查询以及添加修改 （废弃的）
        /*根据条件获取已经填写的治疗列表信息*/
        private List<PatientTreatmentInfo> GetTreatmentRecordes(int pageSize, int pageIndex, string condstr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.GetFullName(Common.PatientSP_GetTreatmentRecordeList),
                pageSize, pageIndex, condstr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = dset.Tables[1].CreateDataReader();
            return CBO.FillCollection<PatientTreatmentInfo>(dr);        
        }
        
        /*获取病人针对某种疾病当前使用的治疗列表（包括以前使用后中间停用，然后当前又开始使用的治疗）*/
        public List<PatientTreatmentInfo> GetCurUsedTreatments(int pageSize, int pageIndex, int prId, out int count)  /*prId表示病人疾病纪录id*/
        {
            string condstr = string.Format(" [patienttreatment].[prid]={0} and [patienttreatment].[state]={1} ", prId, Constants.CURRENT_USING);
            return GetTreatmentRecordes(pageSize, pageIndex, condstr, out count);
        }

        /*获取病人针对某种疾病当前没有使用的治疗列表*/
        public List<PatientTreatmentInfo> GetCurNoUsedTreatments(int pageSize, int pageIndex, int prId, out int count)  /*prId表示病人疾病纪录id*/
        {
            string condstr = string.Format(" [patienttreatment].[prid]={0} and [patienttreatment].[state]={1} and  [patienttreatment].[id] not in " +
                " ( select [patienttreatment].[id] from [patienttreatment] where [patienttreatment].[prid]={0} and [patienttreatment].[state]={2} ) ",
                prId, Constants.STOPED_USE, Constants.CURRENT_USING);

            List<PatientTreatmentInfo> list = GetTreatmentRecordes(pageSize, pageIndex, condstr, out count);
            /*从list中只取出最新的纪录*/
            int lastTreatmentId = -1;
            for (int i = 0; i < count; i++ )
            {
                if (list[i].TreatmentId == lastTreatmentId)    /*只保留最新的*/
                {
                    list.Remove(list[i]);
                    i--;
                    count--;
                }
                else
                {
                    lastTreatmentId = list[i].TreatmentId;
                }
            }

            return list;
        }
        
        /*获取病人针对某种疾病使用某种治疗的所有历史纪录*/
        public List<PatientTreatmentInfo> GetUseHistoryTreatments(int pageSize, int pageIndex, int prId, int tId, out int count)  /*prId表示病人疾病纪录id*/
        {
            string condstr = string.Format(" [patienttreatment].[prid]={0} and [patienttreatment].[treatmentid]={1} ", prId, tId);
            return GetTreatmentRecordes(pageSize, pageIndex, condstr, out count);
        }

        /*添加治疗信息*/
        public bool AddTreatmentRecorde(PatientTreatmentInfo ptInfo)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_AddTreatmentRecorde,
                ptInfo.PRId, ptInfo.TreatmentId, ptInfo.DataValue, ptInfo.Unit, ptInfo.Frequency, ptInfo.State,
                ptInfo.StartDate, ptInfo.EndDate, ptInfo.UpdateDate, ptInfo.UsePurpose, ptInfo.StopReason,
                ptInfo.privacy);
            return cnt > 0;
        }

        /*修改治疗信息*/
        public bool UpdateTreatmentRecorde(PatientTreatmentInfo ptInfo)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatientSP_UpdateTreatmentRecord,
                ptInfo.Id, ptInfo.DataValue, ptInfo.Unit, ptInfo.Frequency, ptInfo.StartDate, ptInfo.EndDate,
                ptInfo.UpdateDate, ptInfo.UsePurpose, ptInfo.StopReason, ptInfo.privacy);
            return cnt > 0;
        }

        /*停止治疗信息*/
        public bool StopTreatmentRecorde(PatientTreatmentInfo ptInfo)
        {
            int cnt = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.PatinetSP_StopTreatmentRecord,
                ptInfo.Id, ptInfo.EndDate, ptInfo.UpdateDate, ptInfo.StopReason, ptInfo.State);
            return cnt > 0;
        }

        /*改变剂量*/
        public bool ChangeTreatmentDosage(PatientTreatmentInfo ptInfo)
        {
            bool retVal = false;
            SqlConnection conn = new SqlConnection(Common.ConnStr);
            
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                /*修改当前纪录的状态（改变剂量）以及结束时间*/
                SqlHelper.ExecuteNonQuery(ts, Common.PatinetSP_StopTreatmentRecord,
                    ptInfo.Id, ptInfo.ActionDate, ptInfo.UpdateDate, ptInfo.StopReason, Constants.CHANGE_DOSAGE);

                /*添加新的一条记录*/
                SqlHelper.ExecuteNonQuery(ts, Common.PatientSP_AddTreatmentRecorde,
                    ptInfo.PRId, ptInfo.TreatmentId, ptInfo.DataValue, ptInfo.Unit, ptInfo.Frequency, Constants.CURRENT_USING,
                    ptInfo.ActionDate, DateTime.MaxValue, ptInfo.UpdateDate, ptInfo.UsePurpose, "",
                    ptInfo.privacy);

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }

            conn.Close();

            return retVal;
        }

        /*删除治疗信息*/
        public bool DelTreatmentRecordes(int[] trIds)
        {
            int cnt = trIds.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.Append(" [id] in (");
            sb.Append(trIds[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(trIds[i]);
            }
            sb.Append(") ");

            int cnt2 = SqlHelper.ExecuteNonQuery(Common.ConnStr,
                 Common.PatientSP_DelTreatmentRecordes, sb.ToString());

            return cnt2 > cnt;
        }

        /*根据治疗的历史纪录id来获取详细信息*/
        public PatientTreatmentInfo GetPatientTreatmentById(int trId)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSP_GetTreatmentRecord,
                trId);
            return CBO.FillObject<PatientTreatmentInfo>(reader);
        }
        #endregion 

        #region 病人检索
        /*根据病人内部条件如：用户名、年龄、地域等来检索病人，并返回用户列表以及总记录数*/
        public List<UserInfo> GetLatestPatientList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetLatestPatientList,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }

        /*根据病人内部条件如：用户名、年龄、地域等来检索病人，并返回用户列表以及总记录数*/
        public List<UserInfo> GetPatientList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetPatientList,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }

        /*根据病人内部条件如：用户名、年龄、地域等来检索病人，并返回用户列表以及总记录数*/
        /* 此函数返回所有的用户信息，即使用户没有指定所患的疾病 */
        public List<UserInfo> GetAllPatientsList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetAllPatientsList,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }

        /*根据疾病信息来检索病人，返回病人列表以及总记录数*/
        public List<UserInfo> GetPatientListByDisease(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetPatientList,
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }

        /*根据治疗信息来检索病人，返回病人列表以及总记录数*/
        public List<UserInfo> GetPatientListByTreatment(int pageSize, int pageIndex, string condStr, string orderStr, int isQueryCurrent, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetPatientListByTreatment,
                pageSize, pageIndex, condStr, orderStr, isQueryCurrent);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }


        /*根据症状信息来检索病人，返回病人列表以及总记录数*/
        public List<UserInfo> GetPatientListBySymptom(int pageSize, int pageIndex, string condStr, string orderStr, int isQueryCurrent, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetPatientListBySymptom,
                pageSize, pageIndex, condStr, orderStr, isQueryCurrent);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }

        /*使用高级搜索来检索病人(多表联合)，返回病人列表以及总记录数*/
        public List<UserInfo> GetPatientListByAdvanceSearch(int pageSize, int pageIndex, string condStr, string orderStr, int isQueryCurrent, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSP_GetPatientListByAdvanceSearch,
                pageSize, pageIndex, condStr, orderStr, isQueryCurrent);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<UserInfo>(dr);
        }
        #endregion

        #region 病人Survey
        //查询某天某种记录的Survey是否存在
        public bool CheckPatientSurvey(int prId, int surveyType, DateTime surveyDate)
        {
            int count = 0;
            string condStr = string.Format(@" [PatientSurvey].[SurveyType] = {0} AND [PatientSurvey].[RecordDate] = '{1}' AND [PatientSurvey].[PRId] = {2}", 
                surveyType, surveyDate.ToString("yyyy-MM-dd"), prId);

            SqlDataReader rdr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSurveySP_GetPatientSurveyCount,
                condStr);

            if (rdr.Read())
            {
                count = rdr.GetInt32(0);
            }
            rdr.Close();

            return count > 0;
        }
        #endregion
    }
}